<?php
	require_once("Service.php");
	require_once("Database.php");
	require_once("vo/UserVO.php");
	require_once("vo/SponsorVO.php");
	require_once("vo/ForumPostVO.php");
	require_once("vo/ForumCommentVO.php");
	require_once("vo/EventVO.php");
	
	/**
	 * Class which handles all group management and viewing related queries.
	 */
	class GroupManagementService extends Service {
		
		var $conn;
		
		/** The constructor of this object. */
		function __construct() {
			
			parent::__construct();
			$this->conn = new Database();
		}
		
		/**
		 * Finds a object containing the profile information of a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group to find out more about.
		 * @return An object containing the group data.
		 */
		function getGroupProfile($sessionID, $groupUID) {
			
			$this->validateSession($sessionID);
			
			$result = array();
			
			// get the groups active modules
			$query = Database::buildQuery("
				SELECT
					Modules.moduleURL,
					CASE
						WHEN GroupModules.groupUID IS NULL
						THEN 0
						ELSE 1
					END AS active
				FROM Modules
				LEFT OUTER JOIN GroupModules
					ON GroupModules.moduleUID = Modules.moduleUID
				WHERE GroupModules.groupUID = ?
			", $groupUID);
			$qryModules = $this->conn->execute($query);
			
			$modules = array();
			while($recModules = mysql_fetch_assoc($qryModules)) {
				$modules[$recModules["moduleURL"]] = ($recModules["active"] == 1? true:false);
			}
			$result["modules"] = $modules;
			
			// get the members of the group
			$query = Database::buildQuery("
				SELECT
					Users.userUID,
					Users.firstName,
					Users.lastName
				FROM UserGroups
				INNER JOIN Users
					ON Users.userUID = UserGroups.userUID
				WHERE UserGroups.groupUID = ?
				AND UserGroups.isBanned = 0
				ORDER BY
					Users.firstName ASC,
					Users.lastName ASC
			", $groupUID);
			$qryMembers = $this->conn->execute($query);
			
			$members = array();
			while($recMembers = mysql_fetch_assoc($qryMembers)) {
				$members[] = UserVO::mapObject($recMembers);
			}
			$result["members"] = $members;
			
			// get the sponsors of the group
			$query = Database::buildQuery("
				SELECT
					sponsorUID,
					groupUID,
					sponsorName,
					description,
					sponsorURL
				FROM GroupSponsors
				WHERE groupUID = ?
				ORDER BY sponsorName ASC
			", $groupUID);
			$qrySponsors = $this->conn->execute($query);
			
			$sponsors = array();
			while($recSponsors = mysql_fetch_assoc($qrySponsors)) {
				$sponsors[] = SponsorVO::mapObject($recSponsors);
			}
			$result["sponsors"] = $sponsors;
			
			// get the latest 5 blog posts
			if($modules["blog"] == true) {
				
				$query = Database::buildQuery("
					SELECT
						BlogPosts.blogPostUID,
						BlogPosts.heading,
						BlogPosts.dateCreated,
						Users.userUID,
						Users.firstName,
						Users.lastName,
						COUNT(BlogComments.blogCommentUID) AS noComments
					FROM BlogPosts
					INNER JOIN Users
						ON Users.userUID = BlogPosts.userUID
					LEFT OUTER JOIN BlogComments
						ON BlogComments.blogPostUID = BlogPosts.blogPostUID
					WHERE BlogPosts.userUID IN (
						SELECT userUID
						FROM UserGroups
						WHERE groupUID = ?
						AND isBanned = 0
					)
					GROUP BY 
						BlogPosts.blogPostUID,
						BlogPosts.heading,
						BlogPosts.dateCreated,
						Users.userUID,
						Users.firstName,
						Users.lastName
					ORDER BY BlogPosts.dateCreated DESC
					LIMIT 5", $groupUID);
				$qryBlogPosts = $this->conn->execute($query);
				
				$blogPosts = array();
				while($recBlogPosts = mysql_fetch_assoc($qryBlogPosts)) {
					$blogPosts[] = $recBlogPosts;
				}
				$result["blogPosts"] = $blogPosts;
			}
			
			// get the latest 5 forum posts
			if($modules["forum"] == true) {
				
				$query = Database::buildQuery("
					SELECT
						ForumPosts.forumPostUID,
						ForumPosts.heading,
						ForumPosts.dateCreated,
						Users.userUID,
						Users.firstName,
						Users.lastName,
						COUNT(ForumComments.forumCommentUID) AS noComments
					FROM ForumPosts
					INNER JOIN Users
						ON Users.userUID = ForumPosts.userUID
					LEFT OUTER JOIN ForumComments
						ON ForumComments.forumPostUID = ForumPosts.forumPostUID
					WHERE ForumPosts.groupUID = ?
					GROUP BY 
						ForumPosts.forumPostUID,
						ForumPosts.heading,
						ForumPosts.dateCreated,
						Users.userUID,
						Users.firstName,
						Users.lastName
					ORDER BY ForumPosts.dateCreated DESC
					LIMIT 5
				", $groupUID);
				$qryForumPosts = $this->conn->execute($query);
				
				$forumPosts = array();
				while($recForumPosts = mysql_fetch_assoc($qryForumPosts)) {
					$forumPosts[] = $recForumPosts;
				}
				$result["forumPosts"] = $forumPosts;
			}
			
			// get chat data
			if($modules["chat"] == true) {
				// not needed at present
			}
			
			// get the latest 3 calendar events
			if($modules["calendar"] == true) {
				
				$query = Database::buildQuery("
					SELECT
						Events.eventUID,
						Events.groupUID,
						Events.eventDate,
						Events.eventName
					FROM Events
					WHERE Events.groupUID = ?
					AND Events.eventDate >= NOW()
					ORDER BY
						Events.eventDate ASC,
						Events.eventName ASC
					LIMIT 3
				", $groupUID);
				$qryEvents = $this->conn->execute($query);
				
				$events = array();
				while($recEvents = mysql_fetch_assoc($qryEvents)) {
					$events[] = EventVO::mapObject($recEvents);
				}
				$result["events"] = $events;
			}
			
			// TODO
			if($modules["mediaGallery"] == true) {
				// TODO: query
			}
			
			// TODO
			if($modules["poll"] == true) {
				// TODO: query
			}
			
			return $result;
		}
		
		/**
		 * Returns the active and banned members of a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $groupUID The group to load.
		 * @return An array containing the group members.
		 */
		function getGroupUsers($sessionID, $groupUID) {
			
			$this->validateSession($sessionID);
			
			$result = array();
			
			// get the active members of the group
			$query = Database::buildQuery("
				SELECT
					Users.userUID,
					Users.firstName,
					Users.lastName,
					Users.email,
					Users.phoneNumber,
					UserGroups.isBanned
				FROM UserGroups
				INNER JOIN Users
					ON Users.userUID = UserGroups.userUID
				WHERE UserGroups.groupUID = ?
				ORDER BY
					Users.firstName ASC,
					Users.lastName ASC
			", $groupUID);
			$qryMembers = $this->conn->execute($query);
			
			$result = array();
			while($recMembers = mysql_fetch_assoc($qryMembers)) {
				
				$row = array();
				$row["userUID"] 	= $recMembers["userUID"];
				$row["firstName"] 	= $recMembers["firstName"];
				$row["lastName"] 	= $recMembers["lastName"];
				$row["email"] 		= $recMembers["email"];
				$row["phoneNumber"] = $recMembers["phoneNumber"];
				$row["isBanned"] 	= ($recMembers["isBanned"] == 1? true:false);
				$result[] = $row;
			}
			
			return $result;
		}
		
		/**
		 * Bans a user from a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $userUID The user to ban.
		 * @param $groupUID The group to be banned from.
		 * @return The userUID of the banned user.
		 */
		function banUser($sessionID, $userUID, $groupUID) {
			
			$this->validateSession($sessionID);
			
			// archive the group
			$query = Database::buildQuery("
				UPDATE UserGroups
				SET isBanned = 1
				WHERE userUID = ?
				AND groupUID = ?
			", $userUID, $groupUID);
			$this->conn->execute($query);
			
			return $userUID;
		}
		
		/**
		 * Un-bans a user from a group.
		 * @param $sessionID The sessionID of the user calling this function.
		 * @param $userUID The user to un-ban.
		 * @param $groupUID The group to be un-banned from.
		 * @return The userUID of the un-banned user.
		 */
		function unbanUser($sessionID, $userUID, $groupUID) {
			
			$this->validateSession($sessionID);
			
			// archive the group
			$query = Database::buildQuery("
				UPDATE UserGroups
				SET isBanned = 0
				WHERE userUID = ?
				AND groupUID = ?
			", $userUID, $groupUID);
			$this->conn->execute($query);
			
			return $userUID;
		}
	}
?>